/**
 * 财务数据模型
 * 处理财务相关的数据库操作
 */
const { query } = require('../config/database');

const financeModel = {
  /**
   * 获取用户的所有财务记录
   * @param {number} userId - 用户ID
   * @param {Object} filters - 过滤条件
   * @returns {Promise<Array>} 财务记录列表
   */
  findAll: async (userId, filters = {}) => {
    // 基础查询
    let sql = `
      SELECT r.*, c.name as category_name, c.color as category_color, c.icon as category_icon
      FROM financial_records r
      LEFT JOIN financial_categories c ON r.category_id = c.id
      WHERE r.user_id = ?
    `;
    
    const queryParams = [userId];
    
    // 添加筛选条件
    if (filters.type !== undefined) {
      sql += ' AND r.type = ?';
      queryParams.push(filters.type);
    }
    
    if (filters.category_id) {
      sql += ' AND r.category_id = ?';
      queryParams.push(filters.category_id);
    }
    
    // 日期范围过滤
    if (filters.start_date) {
      sql += ' AND DATE(r.record_time) >= ?';
      queryParams.push(filters.start_date);
    }
    
    if (filters.end_date) {
      sql += ' AND DATE(r.record_time) <= ?';
      queryParams.push(filters.end_date);
    }
    
    // 排序
    sql += ' ORDER BY r.record_time DESC';
    
    // 分页
    if (filters.limit) {
      sql += ' LIMIT ?';
      queryParams.push(parseInt(filters.limit));
      
      if (filters.offset) {
        sql += ' OFFSET ?';
        queryParams.push(parseInt(filters.offset));
      }
    }
    
    return await query(sql, queryParams);
  },

  /**
   * 根据ID获取财务记录
   * @param {number} id - 记录ID
   * @param {number} userId - 用户ID
   * @returns {Promise<Object|null>} 财务记录对象或null
   */
  findById: async (id, userId) => {
    const sql = `
      SELECT r.*, c.name as category_name, c.color as category_color, c.icon as category_icon
      FROM financial_records r
      LEFT JOIN financial_categories c ON r.category_id = c.id
      WHERE r.id = ? AND r.user_id = ?
    `;
    const records = await query(sql, [id, userId]);
    return records.length ? records[0] : null;
  },

  /**
   * 创建新财务记录
   * @param {Object} recordData - 记录数据
   * @returns {Promise<Object>} 创建的记录
   */
  create: async (recordData) => {
    const sql = `
      INSERT INTO financial_records 
      (user_id, category_id, amount, type, description, record_time) 
      VALUES (?, ?, ?, ?, ?, ?)
    `;
    
    const result = await query(sql, [
      recordData.user_id,
      recordData.category_id || null,
      recordData.amount,
      recordData.type, // 0-支出，1-收入
      recordData.description || null,
      recordData.record_time || new Date()
    ]);
    
    return { id: result.insertId, ...recordData };
  },

  /**
   * 更新财务记录
   * @param {number} id - 记录ID
   * @param {number} userId - 用户ID
   * @param {Object} updateData - 要更新的字段
   * @returns {Promise<boolean>} 更新是否成功
   */
  update: async (id, userId, updateData) => {
    const allowedFields = [
      'category_id', 'amount', 'type', 'description', 'record_time'
    ];
    
    const fields = [];
    const values = [];

    // 构建动态更新SQL
    for (const field of allowedFields) {
      if (updateData[field] !== undefined) {
        fields.push(`${field} = ?`);
        values.push(updateData[field]);
      }
    }

    if (fields.length === 0) return false;

    values.push(id, userId);
    const sql = `UPDATE financial_records SET ${fields.join(', ')} WHERE id = ? AND user_id = ?`;
    const result = await query(sql, values);
    
    return result.affectedRows > 0;
  },

  /**
   * 删除财务记录
   * @param {number} id - 记录ID
   * @param {number} userId - 用户ID
   * @returns {Promise<boolean>} 删除是否成功
   */
  delete: async (id, userId) => {
    const sql = 'DELETE FROM financial_records WHERE id = ? AND user_id = ?';
    const result = await query(sql, [id, userId]);
    return result.affectedRows > 0;
  },

  /**
   * 获取财务分类列表
   * @param {number} userId - 用户ID
   * @param {number} type - 分类类型（0-支出，1-收入）
   * @returns {Promise<Array>} 分类列表
   */
  getCategories: async (userId, type) => {
    let sql = `
      SELECT * FROM financial_categories 
      WHERE (user_id = ? OR user_id IS NULL)
    `;
    
    const queryParams = [userId];
    
    if (type !== undefined) {
      sql += ' AND type = ?';
      queryParams.push(type);
    }
    
    sql += ' ORDER BY name';
    
    return await query(sql, queryParams);
  },

  /**
   * 创建财务分类
   * @param {Object} categoryData - 分类数据
   * @returns {Promise<Object>} 创建的分类
   */
  createCategory: async (categoryData) => {
    const sql = 'INSERT INTO financial_categories (name, type, color, icon, user_id) VALUES (?, ?, ?, ?, ?)';
    const result = await query(sql, [
      categoryData.name,
      categoryData.type, // 0-支出，1-收入
      categoryData.color || null,
      categoryData.icon || null,
      categoryData.user_id
    ]);
    
    return { id: result.insertId, ...categoryData };
  },

  /**
   * 获取财务统计数据
   * @param {number} userId - 用户ID
   * @param {Object} options - 统计选项
   * @returns {Promise<Object>} 统计数据
   */
  getStatistics: async (userId, options = {}) => {
    const { period, startDate, endDate } = options;
    
    let dateCondition;
    
    if (startDate && endDate) {
      dateCondition = `DATE(r.record_time) BETWEEN '${startDate}' AND '${endDate}'`;
    } else {
      switch (period) {
        case 'day':
          dateCondition = 'DATE(r.record_time) = CURDATE()';
          break;
        case 'week':
          dateCondition = 'DATE(r.record_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)';
          break;
        case 'year':
          dateCondition = 'DATE(r.record_time) >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)';
          break;
        default: // month
          dateCondition = 'DATE(r.record_time) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)';
      }
    }
    
    // 总收入和支出
    const summarySQL = `
      SELECT 
        SUM(CASE WHEN r.type = 1 THEN r.amount ELSE 0 END) as total_income,
        SUM(CASE WHEN r.type = 0 THEN r.amount ELSE 0 END) as total_expense,
        SUM(CASE WHEN r.type = 1 THEN r.amount ELSE -r.amount END) as balance
      FROM financial_records r
      WHERE r.user_id = ? AND ${dateCondition}
    `;
    
    // 按分类统计
    const categoriesSQL = `
      SELECT 
        c.id, c.name, c.color, c.icon, r.type,
        SUM(r.amount) as total_amount,
        COUNT(*) as count
      FROM financial_records r
      LEFT JOIN financial_categories c ON r.category_id = c.id
      WHERE r.user_id = ? AND ${dateCondition}
      GROUP BY r.category_id, r.type
      ORDER BY total_amount DESC
    `;
    
    // 时间趋势 (按天分组)
    const trendSQL = `
      SELECT 
        DATE(r.record_time) as date,
        SUM(CASE WHEN r.type = 1 THEN r.amount ELSE 0 END) as income,
        SUM(CASE WHEN r.type = 0 THEN r.amount ELSE 0 END) as expense
      FROM financial_records r
      WHERE r.user_id = ? AND ${dateCondition}
      GROUP BY DATE(r.record_time)
      ORDER BY date
    `;
    
    // 并行执行多个查询
    const [summary, categories, trend] = await Promise.all([
      query(summarySQL, [userId]),
      query(categoriesSQL, [userId]),
      query(trendSQL, [userId])
    ]);
    
    return {
      summary: summary[0] || { total_income: 0, total_expense: 0, balance: 0 },
      categories,
      trend
    };
  }
};

module.exports = financeModel; 